package com.topisv.tms.repository.work;

import java.util.Date;
import java.util.List;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;

import com.topisv.tms.entity.work.WorkOrder;
import com.topisv.tms.entity.work.WorkOrderNoCascade;

/**
 * 作业订单_DAO
 * @author ch
 *
 */
public abstract interface WorkOrderDao extends PagingAndSortingRepository<WorkOrder, Long>, JpaSpecificationExecutor<WorkOrder>
{
	
	@Modifying
	@Query("update WorkOrder set expressNo='' where expressNo=?1")
	void updateExpressNo(String expressNo);
	
	@Modifying
	@Query("update WorkOrder set printStatus='printed' where batchCode=?1")
	void updateByBatchCode(String batch);
	
	@Modifying
	@Query("update WorkOrder t set t.invoiceNo=?1,t.invoiceOperate=?2 where t.id = ?3")
	void updateInvoiceNo(String invoiceNo,String invoiceOperate,long id);
	
	@Query("select t from WorkOrder t where t.taskCode=? order by rankno asc")
	List<WorkOrder> findByTaskCode(String taskCode);
	
	@Query("select t from WorkOrder t where t.batchCode=?1 and t.order.ordeno=?2 ")
	WorkOrder loadOrder(String batchCode, String ordeno);

	@Query("select t from WorkOrder t where t.batchCode=?1 and t.status=?2 and t.cancelStatus='ok' ")
	List<WorkOrder> loadOrderByStatus(String batchCode, String status);

	@Query("select t from WorkOrder t where t.taskCode=? order by rankno asc")
	List<WorkOrder> loadOrderByTaskCode(String taskCode);
	
	@Query("select t from WorkOrder t where t.batchCode=?")
	List<WorkOrder> loadOrderByBatchCode(String batchCode);
	
	@Query("select t from WorkOrder t where t.batchCode in ?1")
	List<WorkOrder> loadOrderByBatchCode(List<String> batchCodeList);
	
	@Query("select t from WorkOrderNoCascade t where t.createTime >= ?1 and t.createTime < ?2 and t.customerCode in ?3 and t.warehouseCode in ?4 and t.status in ?5 ")
	List<WorkOrderNoCascade> loadOrderNoCascadeByStatus(Date fromDate,Date toDate,List<String> customerList,List<String> warehouseList,String status);

	@Query("select t from WorkOrderNoCascade t where t.batchCode in ?1")
	List<WorkOrderNoCascade> loadOrderNoCascadeByBatchCode(List<String> batchCodeList);

	@Query("select t from WorkOrder t where t.order.ordeno=? and t.cancelStatus='ok'")
	WorkOrder loadOrderByOrderno(String orderno);
	
	@Query("select t from WorkOrder t where t.batchCode=?1 and t.order.shopCode in ?2 order by t.order.shopCode")
	List<WorkOrder> loadOrderByBatchCodeAndShopCode(String batchCode,List<String> shopCode);

	@Query("select t from WorkOrder t where t.order.ordeno=?")
	WorkOrder loadByOrderno(String orderno);

	@Query("select t from WorkOrder t where t.taskCode=? and t.printStatus is null and t.cancelStatus = 'ok'")
	List<WorkOrder> loadUnprintedList(String taskCode);

	@Query("select count(t.id) from WorkOrder t where t.taskCode=? and t.printStatus is null and t.cancelStatus = 'ok'")
	Long countUnprintedList(String taskCode);
	
	@Query("select t from WorkOrder t where t.taskCode=? and t.invoiceNo is null and t.needInvoice = '1' and t.cancelStatus='ok' order by rankno asc")
	List<WorkOrder> loadInvoiceUnprintedList(String taskCode);
	
	//@Query("select t from WorkOrder t where t.order.ordeno=?2 and (t.expressNo=?1 or t.ebillNo=?1) and t.pkgLock='unpkg' ")
	@Query("select t from WorkOrder t where t.order.ordeno=?2 and (t.expressNo=?1 or t.ebillNo=?1) and t.pkgLock<>'pkged' ")
	WorkOrder loadByExpressNoAndOrderno(String expressNo, String orderno);

	@Query("select t from WorkOrder t where t.status='9' and t.taskCode=? and t.cancelStatus='ok' ")
	List<WorkOrder> loadUnPkgOrderForTask(String taskCode);
	
	@Query("select t from WorkOrder t where t.taskCode=?1 and (t.expressNo=?2 or t.ebillNo=?2) and t.cancelStatus='ok' ")
	WorkOrder loadByTaskAndExpress(String taskCode,String expressNo);
	
	@Query("select t from WorkOrder t where (t.status='1' or t.status='2' or t.status='3' or t.status='4' or t.status='9') and t.batchCode=? and t.cancelStatus='ok' ")
	List<WorkOrder> loadUnPkgOrderForBatch(String batchCode);

	@Query("select t from WorkOrder t where (t.expressNo=?1 or t.ebillNo=?1) and t.cancelStatus='ok'")
	WorkOrder loadByExpressNo(String expressNo);
	
	@Query("select t from WorkOrder t where t.expressNo=?1 or t.ebillNo=?1 or t.order.ordeno=?1")
	WorkOrder loadByExpressNo1(String expressNo);
	
	/**begin add 20160708 取消单目前是运单号加了“_” by zhangweize**/
	@Query("select t from WorkOrder t where t.expressNo=?1 or t.ebillNo=?1 or t.expressNo=?2 or t.ebillNo=?2")
	WorkOrder loadWorkOrderByExpressNo(String expressNo,String expressNo_);
	/**end  add 20160708 by zhangweize**/
	
	@Query("select t from WorkOrder t where t.warehouseCode=?1 and t.flowType=?2 and t.status='9' and t.goodsInfo=?3 and t.cancelStatus='ok' and t.pkgLock='unpkg' ")
	List<WorkOrder> loadOrderForPkg(String warehouseCode,String flowType,String goodsInfo);

	@Query("select t from WorkOrder t where ( t.order.ordeno=?1 or t.customerOrderno=?1 or t.fromno=?1 ) and t.cancelStatus='ok'")
	WorkOrder loadByOrdernoOrCustomerorderno(String orderno);
	
	
	@Query("select t from WorkOrder t where  t.order.ordeno=?1 or t.customerOrderno=?1 or t.fromno=?1 ")
	WorkOrder loadByAnyOrderno(String orderno);

	/** add bug:515 20161018 */
	@Query("select t.id from WorkOrder t where  t.order.ordeno=?1 or t.customerOrderno=?1 or t.fromno=?1 ")
	Long loadIdByAnyOrderno(String orderno);
	
	/** add bug:515 20161018 */
	@Modifying
    @Query(nativeQuery=true, value="update work_order w set w.print_status=?1, w.express_list = case when w.express_list is null then ?2 else concat(w.express_list, ':', ?2) end, w.express_company=?3, w.ebill_no=?4, w.bill_type=?5, w.print_flag=w.print_flag+?6 where w.id=?7")
    int updateStatusForPrint(String printStatus,String expressList, String companyCode, String ebillNo, String billType, int printFlagIncre, Long id);
    
	@Query("select count(*) from WorkOrder t where t.taskCode=? and t.cancelStatus='ok' and t.status <> '6'")
	Long loadUnsendByTaskCode(String taskCode);
	
	@Query("select count(*) from WorkOrder t where t.batchCode=? and t.cancelStatus='ok' and t.status <> '6' ")
	Long loadUnsendByBatchCode(String batchCode);

	@Query("select t from WorkOrder t where  t.order.ordeno=?1 or t.customerOrderno=?1 or t.fromno=?1 or t.expressNo=?1 or t.ebillNo=?1")
	WorkOrder loadByOrdernoOrExpressno(String no);
	
	@Query("select t from WorkOrder t where  t.customerOrderno=?1 and customerCode = ?2")
	List<WorkOrder> loadByCustomerOrdeno(String customerOrdeno,String customerCode);
	
	@Query("select t from WorkOrder t where  t.customerOrderno=?1 and customerCode = ?2 and cancelStatus='ok'")
	List<WorkOrder> loadByCustomerOrdenoNormal(String customerOrdeno,String customerCode);

	@Query("select r.fromno from WorkOrder r where r.expressNo=?1 or r.ebillNo=?1")
	String findOldOrderNo(String oldExpressNo);
	
	@Query("select r from WorkOrder r where r.fromno=?1")
	List<WorkOrder> findFromno(String fromno);
	
	@Query("select r from WorkOrder r where r.order.ordeno=?1")
	WorkOrder findByOrder(String orderno);

	@Modifying
	@Query("update WorkOrder r set r.status=?1 where r.id=?2")
	void updateStatusByOrder(String status,Long id);
	
	@Modifying
	@Query("update WorkOrder r set r.status=?1 where r.taskCode=?2")
	void updateStatusByTaskCode(String status,String taskCode);
	
	@Modifying
	@Query("update WorkOrder r set r.sendOutStatus=?1 where r.id=?2")
	void updateSendStatusByOrder(String sendOutStatus,Long id);
	
	@Modifying
	@Query("update WorkOrder r set r.guaranteeStatus=?1 where r.id=?2")
	void updateGuaranteeStatusByOrder(String status,Long id);
	
	@Query("select t from WorkOrder t where t.guaranteeCode=? and t.batchCode=? order by rankno asc")
	List<WorkOrder> findByGuaranteeTaskCode(String taskCode,String batchCode);
	
	@Modifying
	@Query("delete from WorkBatch where batchCode = ?1")
	void deleteByBatchCode(String batchCode);
	
	/**
	 * 更新打单时的状态
	 * @param printStatus
	 * @param expressList
	 * @param companyCode
	 * @param expressNo
	 * @param ebillNo
	 * @param billType
	 * @param id void
	 */
	@Modifying
	@Query("update WorkOrder w set w.printStatus = ?1,w.expressList = ?2,w.expressCompany = ?3,w.expressNo = ?4,w.ebillNo=?5, w.billType = ?6 where w.id = ?7")
	void updateForPrint(String printStatus,String expressList,String companyCode,String expressNo,String ebillNo,String billType,Long id);
	
	@Modifying
	@Query("update WorkOrder w set w.printFlag= ?1 where w.id = ?2")
	void updatePrintFlag(int printFlag,Long id);
	
	@Query("select r from WorkOrder r where  (r.expressNo=?3 or r.ebillNo=?3) and r.cancelStatus<>'cancel' ")
	List<WorkOrder> findByExpressNo(String expressNo);
	
	/**
	 * 订单复合
	 * 根据运单号和订单号查询数据
	 * @param orderNo
	 * @param expressNo
	 * @return [参数说明]
	 * 
	 * @return List<WorkOrder> [返回类型说明]
	 * @exception throws [违例类型] [违例说明]
	 * @see [类、类#方法、类#成员]
	 * @author zhangpeijun
	 */
	@Query("select t from WorkOrder t where t.order.ordeno=?1 and (t.expressNo=?2 or t.ebillNo=?2)")
    List<WorkOrder> reCheckOrderNoAndExpressNO(String orderNo,String expressNo);
	

	
	/**
	 * 打印拣货单
	 */
	@Query("select t from WorkOrder t where t.taskCode=?1 and t.stockInfo like ?2   order by rankno")
	List<WorkOrder> loadWorkOrderLike(String taskCode,String stockInfo);
	

	/**
	 * 修改随箱单打印次数
	 * @param taskCodes 订单的taskCode组合
	 */
	@Modifying
	@Query("update WorkOrder set printFormFlag=printFormFlag+1 where taskCode in ?1 ")
	void updatePrintFormFlagByTaskCodes(List<String> taskCodes);

	/**
	 * 修改随箱单打印次数
	 * @param taskCode 订单的taskCode
	 */
	@Modifying
	@Query("update WorkOrder set printFormFlag=printFormFlag+1 where taskCode = ? ")
	void updatePrintFormFlagByTaskCode(String taskCode);
	
	@Query("select t from WorkOrder t where (t.expressNo=?1 or t.ebillNo=?1 or t.order.ordeno=?1) and warehouseCode=?2")
	WorkOrder loadByExpressNoAndWarehouseCode(String logistic,String warehouseCode);

}